using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;

using BUS;
using DTO;
using ExcelLibrary.SpreadSheet;
using DevComponents.DotNetBar;
using Excel = Microsoft.Office.Interop.Excel;

namespace QuanLyThuVien
{
    public partial class FormTemp : Form
    {
        private List<BoPhanDTO> listall; // tat ca bophan lay tu csdl
        private List<BoPhanDTO> listdgv; // danh sach cac bophan gan voi datagridview
        BindingSource bs;
        public FormTemp()
        {
            InitializeComponent();
            listall = BoPhanBUS.laydanhsachbophan_bus();
            initgrid();
        }

        private void initgrid()
        {
            listdgv = null;
            listdgv = new List<BoPhanDTO>();
            listdgv.AddRange(listall);
            bs = new BindingSource();
            bs.DataSource = listdgv;
            dataGridViewX1.DataSource = bs;

            dataGridViewX1.AutoGenerateColumns = false;
            dataGridViewX1.Columns.Clear();

            DataGridViewTextBoxColumn col0 = new DataGridViewTextBoxColumn();
            col0.DataPropertyName = "MaBoPhan";
            col0.ReadOnly = true;
            col0.HeaderText = "ma bo phan";

            dataGridViewX1.Columns.Add(col0);

            DataGridViewTextBoxColumn col1 = new DataGridViewTextBoxColumn();
            col1.DataPropertyName = "TenBoPhan";
            col1.ReadOnly = true;
            col1.HeaderText = "ten bo phan";

            dataGridViewX1.Columns.Add(col1);
        }

        private void dataGridViewX1_DefaultValuesNeeded(object sender, DataGridViewRowEventArgs e)
        {
            e.Row.Cells["MaBoPhan"].Value = 111;
            e.Row.Cells["TenBoPhan"].Value = "tenbophan 111";
        }

        private void buttonX1_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "chi xai duoc .xls|*.xls";
            string filename = "";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                filename = ofd.FileName;
            }
            ofd.Dispose();

            if (string.IsNullOrEmpty(filename)) return;

            Excel.Application xlApp ;
            Excel.Workbook xlWorkBook ;
            Excel.Worksheet xlWorkSheet ;
            Excel.Range range ;

            string str;
            int rCnt = 0;
            int cCnt = 0;

            xlApp = new Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Open(filename, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
            xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);

            range = xlWorkSheet.UsedRange;
            List<BoPhanDTO> listnew = new List<BoPhanDTO>();
            for (rCnt = 1; rCnt <= range.Rows.Count; rCnt++)
            {
                BoPhanDTO bp = new BoPhanDTO();
                for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++)
                {
                    if (cCnt == 1)
                    {
                        int ma = 0;

                        if (int.TryParse((string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2.ToString(), out ma)) bp.Mabophan = ma;
                        else break;
                    }
                    else if (cCnt == 2)
                    {
                        bp.Tenbophan = (string)(range.Cells[rCnt, cCnt] as Excel.Range).Value2.ToString();

                    }

                }
                listnew.Add(bp);
            }
            xlWorkBook.Close(true, null, null);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            ////debug
            //MessageBoxEx.Show("toi day ngon");
            listall = null;
            listall = listnew;
            initgrid();
        }

        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Unable to release the Object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }

            /*OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter="chi xai duoc .xls|*.xls";
            string filename="";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                filename = ofd.FileName;
            }
            ofd.Dispose();

            if (string.IsNullOrEmpty(filename)) return;
            MessageBoxEx.Show("filename=" + filename);
            Workbook wb = null;
            Worksheet ws = null;
            try
            {
                wb = Workbook.Load(filename);
                ws = wb.Worksheets[0];
            }
            catch (Exception)
            {
                MessageBoxEx.Show("exception");
                return;
            }

            if (wb == null) return;

            List<BoPhanDTO> listnew = new List<BoPhanDTO>();
            for (int row = ws.Cells.FirstRowIndex; row <= ws.Cells.LastRowIndex; row++)
            {
                BoPhanDTO bp = new BoPhanDTO();
                for (int col = ws.Cells.FirstColIndex; col <= ws.Cells.LastColIndex; col++)
                {
                    if (col == 0)
                    {
                        int ma = 0;

                        if (int.TryParse(ws.Cells[row, col].StringValue, out ma)) bp.Mabophan = ma;
                    }
                    else if (col == 1)
                    {
                        bp.Tenbophan = ws.Cells[row, col].StringValue;
                    }

                }
                listnew.Add(bp);
            }
            //debug
            MessageBoxEx.Show("toi day ngon");
            listall = null;
            listall = listnew;
            initgrid();
             */

        private void buttonX2_Click(object sender, EventArgs e)
        {
            // creating Excel Application
            Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();
            // creating new WorkBook within Excel application
            Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);
            // creating new Excelsheet in workbook
            Microsoft.Office.Interop.Excel._Worksheet worksheet = null;
            // see the excel sheet behind the program
            //Funny
            app.Visible = true;
            // get the reference of first sheet. By default its name is Sheet1.
            // store its reference to worksheet
            try
            {
                //Fixed:(Microsoft.Office.Interop.Excel.Worksheet)
                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Sheets["Sheet1"];
                worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.ActiveSheet;
                // changing the name of active sheet
                worksheet.Name = "Exported from Ketoan";
                // storing header part in Excel
                for (int i = 1; i < dataGridViewX1.Columns.Count + 1; i++)
                {
                    worksheet.Cells[1, i] = dataGridViewX1.Columns[i - 1].HeaderText;
                }
                // storing Each row and column value to excel sheet
                for (int i = 0; i < dataGridViewX1.Rows.Count - 1; i++)
                {
                    for (int j = 0; j < dataGridViewX1.Columns.Count; j++)
                    {
                        worksheet.Cells[i + 2, j + 1] = dataGridViewX1.Rows[i].Cells[j].Value.ToString();
                    }
                }

                // save the application
                string fileName = String.Empty;
                SaveFileDialog saveFileExcel = new SaveFileDialog();

                saveFileExcel.Filter = "Excel files |*.xls|All files (*.*)|*.*";
                saveFileExcel.FilterIndex = 2;
                saveFileExcel.RestoreDirectory = true;

                if (saveFileExcel.ShowDialog() == DialogResult.OK)
                {
                    fileName = saveFileExcel.FileName;
                    //Fixed-old code :11 para->add 1:Type.Missing
                    workbook.SaveAs(fileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);

                }
                else
                    return;

                // Exit from the application
                //app.Quit();
            }
            catch
            {

            }
            finally
            {
                app.Quit();
                workbook = null;
                app = null;
            }
        }
    }
}